package com.headline.dao;

import com.headline.common.Common;
import com.headline.common.DateHelper;
import com.headline.common.Log;
import com.headline.common.PageObject;
import com.headline.dbutil.DbManager;
import com.headline.model.RecommondArticleResult;
import com.headline.model.SpiderNewsInfoEntity;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * Created with Administrator
 * DATE:2016/11/7
 * Time:16:08
 */
public class SpiderNewsInfoDao {

    static Log log = Log.getLogger();


    public int insertSpiderNewsInfo(SpiderNewsInfoEntity spiderNewsInfoEntity) {
        int n = 0;
        String sql = " insert into [spider_news_info] (url,title,picurl,picurl_list,summary,main,source,author,tags,publishtime,is_video,video_duration,article_type,picurl_list2) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            Object[] params = {spiderNewsInfoEntity.getUrl(), spiderNewsInfoEntity.getTitle(),
                    spiderNewsInfoEntity.getPicurl(), spiderNewsInfoEntity.getPicurl_list(), spiderNewsInfoEntity.getSummary(),
                    spiderNewsInfoEntity.getMain(), spiderNewsInfoEntity.getSource(), spiderNewsInfoEntity.getAuthor(), spiderNewsInfoEntity.getTags(),
                    spiderNewsInfoEntity.getPublishtime(), spiderNewsInfoEntity.getIsVideo(), spiderNewsInfoEntity.getVideoDuration(),
                    spiderNewsInfoEntity.getArticle_type(), spiderNewsInfoEntity.getPicurlList2()
            };
            n = queryRunner.update(conn, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("入库失败:" + e.getMessage());
            Common.ShowMessage(e.getMessage());
        }
        DbUtils.closeQuietly(conn);
        return n;
    }

    public List<SpiderNewsInfoEntity> queryList(String strWhere) {
        List<SpiderNewsInfoEntity> list = null;
        String sql = " select * from [spider_news_info] where 1=1 ";
        if (strWhere != "") {
            sql += strWhere;
        }

        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            list = queryRunner.query(conn, sql, new BeanListHandler<>(SpiderNewsInfoEntity.class));
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("查询失败:" + e.getMessage());
        }
        return list;
    }

    public SpiderNewsInfoEntity querySpiderNewsInfoById(int id) {
        SpiderNewsInfoEntity spiderNewsInfoEntity = null;
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from  [spider_news_info] where id=?";
        Object params[] = {id};
        try {
            ResultSetHandler<SpiderNewsInfoEntity> resultSetHandler = new BeanHandler<>(SpiderNewsInfoEntity.class);
            spiderNewsInfoEntity = queryRunner.query(conn, sql, resultSetHandler, params);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            log.logger.error("查询失败:" + e.getMessage());
            e.printStackTrace();
        }
        return spiderNewsInfoEntity;
    }

    public int updateSpiderNewsInfo(SpiderNewsInfoEntity spiderNewsInfoEntity) {
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "update [spider_news_info] set [url]= ? , [title]=? , picurl=? ,picurl_list =? where [id] = ?";
        Object params[] = {spiderNewsInfoEntity.getUrl(), spiderNewsInfoEntity.getTitle(), spiderNewsInfoEntity.getPicurl(), spiderNewsInfoEntity.getPicurl_list(), spiderNewsInfoEntity.getId()};
        try {
            int result = queryRunner.update(conn, sql, params);
            DbUtils.closeQuietly(conn);
            return result;

        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("更新失败:" + e.getMessage());
        }
        return 0;
    }


    /**
     * 删除文章
     *
     * @param id
     * @return
     */
    public int deleteSpiderNewsInfoById(int id) {
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "delete from [spider_news_info] where id =?";
        try {
            int delResult = queryRunner.update(conn, sql, id);
            DbUtils.closeQuietly(conn);
            return delResult;
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("删除失败:" + e.getMessage());
        }
        return 0;
    }

    public PageObject queryPage(int pageIndex, int pageSize) {

        PageObject pageObject = new PageObject();
        Connection conn = DbManager.getConnection();
        String sql_1 = "select count(1) from [spider_news_info]";
        String sql_2 = "SELECT TOP " + pageSize + " * " +
                "FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id desc ) AS RowNumber,* FROM dbo.[spider_news_info]) as A WHERE RowNumber > " + pageSize + "*(" + pageIndex + "-1) ";

        QueryRunner queryRunner = new QueryRunner();
        try {
            Object o = queryRunner.query(conn, sql_1, new ScalarHandler());
            int rows = Integer.parseInt(o.toString());
            int pageCount = rows / pageSize + (rows % pageSize == 0 ? 0 : 1);
            List<SpiderNewsInfoEntity> list = queryRunner.query(conn, sql_2, new BeanListHandler<>(SpiderNewsInfoEntity.class));

            pageObject.setAbsolutePage(pageCount);
            pageObject.setCurrentPage(pageIndex);
            pageObject.setDatasource(list);
            pageObject.setPageSize(pageSize);
            pageObject.setTotalCount(rows);
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("分页查询失败:" + e.getMessage());
        }
        return pageObject;

    }

    public List<RecommondArticleResult> queryApp(int max_id, int since_id) {
        int act = 0;  // 0上拉 1下拉
        List<RecommondArticleResult> list = new ArrayList<>();
        List<SpiderNewsInfoEntity> listSource;
        int pageSize = 10;
        String sql = "";
        if (max_id == 0 && since_id == 0) {
            pageSize = 20;
            sql = "select top " + pageSize + " * from spider_news_info order by id asc";

        } else {
            pageSize = 10;
            if (max_id > 0) {
                act = 1;
                sql = "select top " + pageSize + " * from spider_news_info where 1=1 and id <" + max_id + " order by id desc";
            }
            if (since_id > 0) {

                sql = "select top " + pageSize + " * from spider_news_info where 1=1 and id >" + since_id + " order by id asc";
            }
        }
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            listSource = queryRunner.query(conn, sql, new BeanListHandler<>(SpiderNewsInfoEntity.class));
            if (act == 0) {
                Collections.reverse(listSource);//倒叙
            }
            if (listSource != null && listSource.size() > 0) {
                for (SpiderNewsInfoEntity model : listSource) {
                    RecommondArticleResult recommondArticleResult = new RecommondArticleResult();
                    try {
                        recommondArticleResult.setId(model.getId());
                        recommondArticleResult.setClick(1);
                        recommondArticleResult.setIs_ad(0);
                        recommondArticleResult.setOpenscheme("");
                        recommondArticleResult.setPic_url(model.getPicurl() == null ? "" : model.getPicurl());
                        recommondArticleResult.setPub_time(model.getPublishtime());
                        recommondArticleResult.setTitle(model.getTitle());
                        recommondArticleResult.setSource(model.getSource());
                        recommondArticleResult.setRefresh_time(model.getCreatetime());
                        recommondArticleResult.setArticle_type(model.getArticle_type());
                        java.util.Date d = model.getCreatetime();
                        recommondArticleResult.setRefresh_time_tick(DateHelper.dateToTimeSpan(d.toString()));
                        List<String> listPics = new ArrayList<>();
                        if (model.getPicurl_list() != null) {
                            String pics = model.getPicurl_list();
                            if (!pics.equals("")) {
                                String[] arryPic = pics.split(",");
                                for (String i : arryPic) {
                                    if (i != "")
                                        listPics.add(i);
                                }
                                recommondArticleResult.setArticle_pics(listPics);
                            }
                        } else {
                            recommondArticleResult.setArticle_pics(null);
                        }
                        list.add(recommondArticleResult);
                    } catch (Exception e) {
                        log.logger.error("装载失败:" + e.toString());
                    }
                }
            }
        } catch (SQLException e) {
            log.logger.error("queryApp异常： " + e.getMessage());
        }
        return list;

    }

}
